using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using iBeauty.BusinessObjects;

namespace iBeauty.DAL
{
    public class CustomerDAO
    {
        public CustomerDAO()
        {
            DbProviderHelper.GetConnection();
        }

        public bool CheckExitsCustomer(string customerCode)
        {
            var oDbCommand = DbProviderHelper.CreateCommand("SELECT Count([ID]) FROM [Customer] WHERE [CustomerCode] = @CustomerCode", CommandType.Text);

            oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, customerCode));

            var result = (long) DbProviderHelper.ExecuteScalar(oDbCommand);

            return (result > 0);
        }

        public string GetLastID()
        {
            var oDbCommand = DbProviderHelper.CreateCommand("SELECT customercode FROM Customer ORDER BY  id DESC LIMIT 1", CommandType.Text);
            var result = (string)DbProviderHelper.ExecuteScalar(oDbCommand);
            return result;
        }

        
        public List<Customer> GetCustomers()
        {
            try
            {
                List<Customer> lstCustomers = new List<Customer>();
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [ID],[CreateDate],[ModifiedDate],[TimeStamp],[CustomerCode],[CustomerName],[Barcode],[Email],[Address],[Phone],[Pictures],[Born],[Gender],[Job],[Nationlaty],[BloodType],[FirstVisit],[LastVisit],[AccountId],[Description] FROM [Customer]", CommandType.Text);
                DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
                while (oDbDataReader.Read())
                {
                    Customer oCustomer = new Customer();
                    oCustomer.ID = Convert.ToString(oDbDataReader["ID"]);

                    if (oDbDataReader["CreateDate"] != DBNull.Value)
                        oCustomer.CreateDate = Convert.ToDateTime(oDbDataReader["CreateDate"]);

                    if (oDbDataReader["ModifiedDate"] != DBNull.Value)
                        oCustomer.ModifiedDate = Convert.ToDateTime(oDbDataReader["ModifiedDate"]);

                    if (oDbDataReader["TimeStamp"] != DBNull.Value)
                        oCustomer.TimeStamp = Convert.ToDateTime(oDbDataReader["TimeStamp"]);

                    if (oDbDataReader["CustomerCode"] != DBNull.Value)
                        oCustomer.CustomerCode = Convert.ToString(oDbDataReader["CustomerCode"]);

                    if (oDbDataReader["CustomerName"] != DBNull.Value)
                        oCustomer.CustomerName = Convert.ToString(oDbDataReader["CustomerName"]);

                    if (oDbDataReader["Barcode"] != DBNull.Value)
                        oCustomer.Barcode = Convert.ToString(oDbDataReader["Barcode"]);

                    if (oDbDataReader["Email"] != DBNull.Value)
                        oCustomer.Email = Convert.ToString(oDbDataReader["Email"]);

                    if (oDbDataReader["Address"] != DBNull.Value)
                        oCustomer.Address = Convert.ToString(oDbDataReader["Address"]);

                    if (oDbDataReader["Phone"] != DBNull.Value)
                        oCustomer.Phone = Convert.ToString(oDbDataReader["Phone"]);

                    if (oDbDataReader["Pictures"] != DBNull.Value)
                        oCustomer.Pictures = Convert.ToString(oDbDataReader["Pictures"]);

                    if (oDbDataReader["Born"] != DBNull.Value)
                        oCustomer.Born = Convert.ToDateTime(oDbDataReader["Born"]);

                    if (oDbDataReader["Gender"] != DBNull.Value)
                        oCustomer.Gender = Convert.ToString(oDbDataReader["Gender"]);

                    if (oDbDataReader["Job"] != DBNull.Value)
                        oCustomer.Job = Convert.ToInt32(oDbDataReader["Job"]);

                    if (oDbDataReader["Nationlaty"] != DBNull.Value)
                        oCustomer.Nationlaty = Convert.ToString(oDbDataReader["Nationlaty"]);

                    if (oDbDataReader["BloodType"] != DBNull.Value)
                        oCustomer.BloodType = Convert.ToString(oDbDataReader["BloodType"]);

                    if (oDbDataReader["FirstVisit"] != DBNull.Value)
                        oCustomer.FirstVisit = Convert.ToDateTime(oDbDataReader["FirstVisit"]);

                    if (oDbDataReader["LastVisit"] != DBNull.Value)
                        oCustomer.LastVisit = Convert.ToDateTime(oDbDataReader["LastVisit"]);

                    if (oDbDataReader["AccountId"] != DBNull.Value)
                        oCustomer.AccountId = Convert.ToInt32(oDbDataReader["AccountId"]);

                    if (oDbDataReader["Description"] != DBNull.Value)
                        oCustomer.Description = Convert.ToString(oDbDataReader["Description"]);
                    lstCustomers.Add(oCustomer);
                }
                oDbDataReader.Close();
                return lstCustomers;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Customer GetCustomer(string ID)
        {
            try
            {
                Customer oCustomer = new Customer();
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [ID],[CreateDate],[ModifiedDate],[TimeStamp],[CustomerCode],[CustomerName],[Barcode],[Email],[Address],[Phone],[Pictures],[Born],[Gender],[Job],[Nationlaty],[BloodType],[FirstVisit],[LastVisit],[AccountId],[Description] FROM [Customer] WHERE [ID]=@ID", CommandType.Text);
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID", DbType.String, ID));
                DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
                while (oDbDataReader.Read())
                {
                    oCustomer.ID = Convert.ToString(oDbDataReader["ID"]);

                    if (oDbDataReader["CreateDate"] != DBNull.Value)
                        oCustomer.CreateDate = Convert.ToDateTime(oDbDataReader["CreateDate"]);

                    if (oDbDataReader["ModifiedDate"] != DBNull.Value)
                        oCustomer.ModifiedDate = Convert.ToDateTime(oDbDataReader["ModifiedDate"]);

                    if (oDbDataReader["TimeStamp"] != DBNull.Value)
                        oCustomer.TimeStamp = Convert.ToDateTime(oDbDataReader["TimeStamp"]);

                    if (oDbDataReader["CustomerCode"] != DBNull.Value)
                        oCustomer.CustomerCode = Convert.ToString(oDbDataReader["CustomerCode"]);

                    if (oDbDataReader["CustomerName"] != DBNull.Value)
                        oCustomer.CustomerName = Convert.ToString(oDbDataReader["CustomerName"]);

                    if (oDbDataReader["Barcode"] != DBNull.Value)
                        oCustomer.Barcode = Convert.ToString(oDbDataReader["Barcode"]);

                    if (oDbDataReader["Email"] != DBNull.Value)
                        oCustomer.Email = Convert.ToString(oDbDataReader["Email"]);

                    if (oDbDataReader["Address"] != DBNull.Value)
                        oCustomer.Address = Convert.ToString(oDbDataReader["Address"]);

                    if (oDbDataReader["Phone"] != DBNull.Value)
                        oCustomer.Phone = Convert.ToString(oDbDataReader["Phone"]);

                    if (oDbDataReader["Pictures"] != DBNull.Value)
                        oCustomer.Pictures = Convert.ToString(oDbDataReader["Pictures"]);

                    if (oDbDataReader["Born"] != DBNull.Value)
                        oCustomer.Born = Convert.ToDateTime(oDbDataReader["Born"]);

                    if (oDbDataReader["Gender"] != DBNull.Value)
                        oCustomer.Gender = Convert.ToString(oDbDataReader["Gender"]);

                    if (oDbDataReader["Job"] != DBNull.Value)
                        oCustomer.Job = Convert.ToInt32(oDbDataReader["Job"]);

                    if (oDbDataReader["Nationlaty"] != DBNull.Value)
                        oCustomer.Nationlaty = Convert.ToString(oDbDataReader["Nationlaty"]);

                    if (oDbDataReader["BloodType"] != DBNull.Value)
                        oCustomer.BloodType = Convert.ToString(oDbDataReader["BloodType"]);

                    if (oDbDataReader["FirstVisit"] != DBNull.Value)
                        oCustomer.FirstVisit = Convert.ToDateTime(oDbDataReader["FirstVisit"]);

                    if (oDbDataReader["LastVisit"] != DBNull.Value)
                        oCustomer.LastVisit = Convert.ToDateTime(oDbDataReader["LastVisit"]);

                    if (oDbDataReader["AccountId"] != DBNull.Value)
                        oCustomer.AccountId = Convert.ToInt32(oDbDataReader["AccountId"]);

                    if (oDbDataReader["Description"] != DBNull.Value)
                        oCustomer.Description = Convert.ToString(oDbDataReader["Description"]);
                }
                oDbDataReader.Close();
                return oCustomer;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int AddCustomer(string ID, DateTime? CreateDate, DateTime? ModifiedDate, DateTime? TimeStamp, string CustomerCode, string CustomerName, string Barcode, string Email, string Address, string Phone, string Pictures, DateTime? Born, string Gender, int? Job, string Nationlaty, string BloodType, DateTime? FirstVisit, DateTime? LastVisit, int? AccountId, string Description)
        {
            try
            {
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Customer]([CreateDate],[ModifiedDate],[TimeStamp],[CustomerCode],[CustomerName],[Barcode],[Email],[Address],[Phone],[Pictures],[Born],[Gender],[Job],[Nationlaty],[BloodType],[FirstVisit],[LastVisit],[AccountId],[Description])VALUES(@CreateDate,@ModifiedDate,@TimeStamp,@CustomerCode,@CustomerName,@Barcode,@Email,@Address,@Phone,@Pictures,@Born,@Gender,@Job,@Nationlaty,@BloodType,@FirstVisit,@LastVisit,@AccountId,@Description)", CommandType.Text);
                if (CreateDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, CreateDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, DBNull.Value));
                if (ModifiedDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, ModifiedDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, DBNull.Value));
                if (TimeStamp.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, TimeStamp));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, DBNull.Value));
                if (CustomerCode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, CustomerCode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, DBNull.Value));
                if (CustomerName != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, CustomerName));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, DBNull.Value));
                if (Barcode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, Barcode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, DBNull.Value));
                if (Email != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, Email));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, DBNull.Value));
                if (Address != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, Address));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, DBNull.Value));
                if (Phone != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, Phone));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, DBNull.Value));
                if (Pictures != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, Pictures));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, DBNull.Value));
                if (Born.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, Born));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, DBNull.Value));
                if (Gender != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, Gender));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, DBNull.Value));
                if (Job.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, Job));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, DBNull.Value));
                if (Nationlaty != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, Nationlaty));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, DBNull.Value));
                if (BloodType != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, BloodType));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, DBNull.Value));
                if (FirstVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, FirstVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, DBNull.Value));
                if (LastVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, LastVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, DBNull.Value));
                if (AccountId.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, AccountId));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, DBNull.Value));
                if (Description != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, Description));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, DBNull.Value));

                return DbProviderHelper.ExecuteNonQuery(oDbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int AddCustomer(Customer oCustomer)
        {
            try
            {
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [Customer]([CreateDate],[ModifiedDate],[TimeStamp],[CustomerCode],[CustomerName],[Barcode],[Email],[Address],[Phone],[Pictures],[Born],[Gender],[Job],[Nationlaty],[BloodType],[FirstVisit],[LastVisit],[AccountId],[Description])VALUES(@CreateDate,@ModifiedDate,@TimeStamp,@CustomerCode,@CustomerName,@Barcode,@Email,@Address,@Phone,@Pictures,@Born,@Gender,@Job,@Nationlaty,@BloodType,@FirstVisit,@LastVisit,@AccountId,@Description)", CommandType.Text);
                if (oCustomer.CreateDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, oCustomer.CreateDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, DBNull.Value));
                if (oCustomer.ModifiedDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, oCustomer.ModifiedDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, DBNull.Value));
                if (oCustomer.TimeStamp.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, oCustomer.TimeStamp));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, DBNull.Value));
                if (oCustomer.CustomerCode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, oCustomer.CustomerCode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, DBNull.Value));
                if (oCustomer.CustomerName != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, oCustomer.CustomerName));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, DBNull.Value));
                if (oCustomer.Barcode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, oCustomer.Barcode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, DBNull.Value));
                if (oCustomer.Email != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, oCustomer.Email));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, DBNull.Value));
                if (oCustomer.Address != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, oCustomer.Address));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, DBNull.Value));
                if (oCustomer.Phone != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, oCustomer.Phone));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, DBNull.Value));
                if (oCustomer.Pictures != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, oCustomer.Pictures));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, DBNull.Value));
                if (oCustomer.Born.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, oCustomer.Born));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, DBNull.Value));
                if (oCustomer.Gender != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, oCustomer.Gender));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, DBNull.Value));
                if (oCustomer.Job.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, oCustomer.Job));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, DBNull.Value));
                if (oCustomer.Nationlaty != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, oCustomer.Nationlaty));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, DBNull.Value));
                if (oCustomer.BloodType != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, oCustomer.BloodType));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, DBNull.Value));
                if (oCustomer.FirstVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, oCustomer.FirstVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, DBNull.Value));
                if (oCustomer.LastVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, oCustomer.LastVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, DBNull.Value));
                if (oCustomer.AccountId.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, oCustomer.AccountId));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, DBNull.Value));
                if (oCustomer.Description != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, oCustomer.Description));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, DBNull.Value));

                return DbProviderHelper.ExecuteNonQuery(oDbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateCustomer(string ID, DateTime? CreateDate, DateTime? ModifiedDate, DateTime? TimeStamp, string CustomerCode, string CustomerName, string Barcode, string Email, string Address, string Phone, string Pictures, DateTime? Born, string Gender, int? Job, string Nationlaty, string BloodType, DateTime? FirstVisit, DateTime? LastVisit, int? AccountId, string Description)
        {

            try
            {
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Customer] SET [CreateDate]=@CreateDate,[ModifiedDate]=@ModifiedDate,[TimeStamp]=@TimeStamp,[CustomerCode]=@CustomerCode,[CustomerName]=@CustomerName,[Barcode]=@Barcode,[Email]=@Email,[Address]=@Address,[Phone]=@Phone,[Pictures]=@Pictures,[Born]=@Born,[Gender]=@Gender,[Job]=@Job,[Nationlaty]=@Nationlaty,[BloodType]=@BloodType,[FirstVisit]=@FirstVisit,[LastVisit]=@LastVisit,[AccountId]=@AccountId,[Description]=@Description WHERE [ID]=@ID", CommandType.Text);
                if (CreateDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, CreateDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, DBNull.Value));
                if (ModifiedDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, ModifiedDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, DBNull.Value));
                if (TimeStamp.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, TimeStamp));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, DBNull.Value));
                if (CustomerCode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, CustomerCode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, DBNull.Value));
                if (CustomerName != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, CustomerName));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, DBNull.Value));
                if (Barcode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, Barcode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, DBNull.Value));
                if (Email != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, Email));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, DBNull.Value));
                if (Address != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, Address));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, DBNull.Value));
                if (Phone != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, Phone));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, DBNull.Value));
                if (Pictures != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, Pictures));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, DBNull.Value));
                if (Born.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, Born));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, DBNull.Value));
                if (Gender != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, Gender));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, DBNull.Value));
                if (Job.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, Job));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, DBNull.Value));
                if (Nationlaty != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, Nationlaty));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, DBNull.Value));
                if (BloodType != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, BloodType));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, DBNull.Value));
                if (FirstVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, FirstVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, DBNull.Value));
                if (LastVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, LastVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, DBNull.Value));
                if (AccountId.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, AccountId));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, DBNull.Value));
                if (Description != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, Description));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, DBNull.Value));
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID", DbType.String, ID));
                return DbProviderHelper.ExecuteNonQuery(oDbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateCustomer(Customer oCustomer)
        {

            try
            {
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [Customer] SET [CreateDate]=@CreateDate,[ModifiedDate]=@ModifiedDate,[TimeStamp]=@TimeStamp,[CustomerCode]=@CustomerCode,[CustomerName]=@CustomerName,[Barcode]=@Barcode,[Email]=@Email,[Address]=@Address,[Phone]=@Phone,[Pictures]=@Pictures,[Born]=@Born,[Gender]=@Gender,[Job]=@Job,[Nationlaty]=@Nationlaty,[BloodType]=@BloodType,[FirstVisit]=@FirstVisit,[LastVisit]=@LastVisit,[AccountId]=@AccountId,[Description]=@Description WHERE [ID]=@ID", CommandType.Text);
                if (oCustomer.CreateDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, oCustomer.CreateDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CreateDate", DbType.DateTime, DBNull.Value));
                if (oCustomer.ModifiedDate.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, oCustomer.ModifiedDate));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ModifiedDate", DbType.DateTime, DBNull.Value));
                if (oCustomer.TimeStamp.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, oCustomer.TimeStamp));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@TimeStamp", DbType.DateTime, DBNull.Value));
                if (oCustomer.CustomerCode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, oCustomer.CustomerCode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerCode", DbType.String, DBNull.Value));
                if (oCustomer.CustomerName != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, oCustomer.CustomerName));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@CustomerName", DbType.String, DBNull.Value));
                if (oCustomer.Barcode != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, oCustomer.Barcode));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Barcode", DbType.String, DBNull.Value));
                if (oCustomer.Email != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, oCustomer.Email));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Email", DbType.String, DBNull.Value));
                if (oCustomer.Address != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, oCustomer.Address));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Address", DbType.String, DBNull.Value));
                if (oCustomer.Phone != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, oCustomer.Phone));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Phone", DbType.String, DBNull.Value));
                if (oCustomer.Pictures != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, oCustomer.Pictures));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Pictures", DbType.String, DBNull.Value));
                if (oCustomer.Born.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, oCustomer.Born));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Born", DbType.DateTime, DBNull.Value));
                if (oCustomer.Gender != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, oCustomer.Gender));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Gender", DbType.String, DBNull.Value));
                if (oCustomer.Job.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, oCustomer.Job));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Job", DbType.Int32, DBNull.Value));
                if (oCustomer.Nationlaty != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, oCustomer.Nationlaty));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Nationlaty", DbType.String, DBNull.Value));
                if (oCustomer.BloodType != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, oCustomer.BloodType));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@BloodType", DbType.String, DBNull.Value));
                if (oCustomer.FirstVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, oCustomer.FirstVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@FirstVisit", DbType.DateTime, DBNull.Value));
                if (oCustomer.LastVisit.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, oCustomer.LastVisit));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@LastVisit", DbType.DateTime, DBNull.Value));
                if (oCustomer.AccountId.HasValue)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, oCustomer.AccountId));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@AccountId", DbType.Int32, DBNull.Value));
                if (oCustomer.Description != null)
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, oCustomer.Description));
                else
                    oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@Description", DbType.String, DBNull.Value));
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID", DbType.String, oCustomer.ID));
                return DbProviderHelper.ExecuteNonQuery(oDbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int RemoveCustomer(Customer oCustomer)
        {

            try
            {
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Customer] WHERE [ID]=@ID", CommandType.Text);
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID", DbType.String, oCustomer.ID));
                return DbProviderHelper.ExecuteNonQuery(oDbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int RemoveCustomer(string ID)
        {

            try
            {
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [Customer] WHERE [ID]=@ID", CommandType.Text);
                oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@ID", DbType.String, ID));
                return DbProviderHelper.ExecuteNonQuery(oDbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
